Connecting to the Database via Excel

Excel is able to run SQL queries for users that have access to the database server. In this example, we’ll connect to the DSS database with Excel 365. Other versions of Excel have a similar function and can be searched on the web. This connection will be the framework for the other guides in this series.

  1. Open Excel and create a new sheet.

  2. Navigate to the Data tab. Navigate to Get Data > From Database > From SQL Server Database.

  3. Enter the database information for the server and database name of the DSS database. Click OK.

  4. At this point, the user is prompted with an authentication challenge. Only users who can read from the DSS database (Windows or SQL) can use this function. In this example, we will use a Database account instead of a Windows account. After entering the credentials, click Connect.

  5. You may receive an Encrypted Connection message and can click through this.

  6. A list of several tables and views will appear and we’ll work with several of these in this series of guides.

  7. For this exercise, we will connect to BI.DataSecurityAssignedtoUser. Highlight that view and click Load.

  8. The data will load into a table that is filterable by user, data element type, and function.

  9. The Refresh button at the top would update your data based on the current data in the DSS database.
  10. To take this a step further for usability, adding another sheet in Excel would allow you to create a pivot table based on the data in the prior steps. Create a new sheet.
  11. Click on the Insert tab in the header of Excel.

  12. Click on Pivot Table and choose Use an external data source.

  13. Click Choose Connection. When the prompt appears, choose the connection from inside of this workbook. Click Open.

  14. Click OK. You will now see the standard pivot table interface.

  15. Using the standard pivot table function areas of Filters, Columns, Rows and Values, we can now build out a filterable report. Please note that you can drag and drop the fields into any of the four regions. In the example below, an Hours Code Security Report has been created to show what users have access to what hours codes. Using standard Excel functionality, you can now filter on this data for each of the columns.